package dao

import (
	"log"
	"strings"
	"time"
	"yundian/internel/app/helper"
	"yundian/internel/app/helper/request"
	"yundian/internel/app/models/statistics"
	"yundian/internel/pkg/dingo"
	"yundian/internel/pkg/dingo/global"
	"yundian/internel/pkg/wechatpay"
)

type StatisticsDao struct {
}

func NewStatisticsDao() *StatisticsDao {
	return &StatisticsDao{}
}

type CountSettlement struct {
	Total int64
	Money int64
}

// 门店总数
func (d *StatisticsDao) QueryTotalPlaces(dateStr string) *dingo.ErrorWrap {
	var total int64
	db := global.Orm.Table("places").Select("count(1) as total")
	db.Where("DATE_FORMAT(FROM_UNIXTIME(create_at),'%Y-%m-%d') <= ?", dateStr)
	db.Find(&total)
	return dingo.ErrorIf(db.Error, total)
}

// 设备总数
func (d *StatisticsDao) QueryTotalDevices(dateStr string) *dingo.ErrorWrap {
	var total int64
	sql := "SELECT count(1) FROM devices WHERE state != 1 and" +
		" place_id IN (SELECT id FROM places WHERE DATE_FORMAT(FROM_UNIXTIME(create_at),'%Y-%m-%d') <= ?)"
	db := global.Orm.Raw(sql, dateStr).Find(&total)
	return dingo.ErrorIf(db.Error, total)
}

// QueryTotalOrderByState 查询订单总数-状态
func (d *StatisticsDao) QueryTotalOrderByState(state int, dateStr string) *dingo.ErrorWrap {
	var m CountSettlement
	db := global.Orm.Table("temp_orders_" + helper.ConvSpecStr(dateStr)).Select("count(1) as total, SUM(settlement_money) as money")
	if state > 0 { db.Where("state = ?", state) } // 按状态查询
	db.Where("is_tianhai = 0")
	db.Find(&m)
	return dingo.ErrorIf(db.Error, m)
}

// QueryTotalOrderByState 查询订单总数-抽单
func (d *StatisticsDao) QueryTotalOrderByDraw(isDraw, state int, dateStr string) *dingo.ErrorWrap {
	var m CountSettlement
	db := global.Orm.Table("temp_orders_" + helper.ConvSpecStr(dateStr)).Select("count(1) as total, SUM(settlement_money) as money")
	if isDraw == 1 { db.Where("is_draw = ? and is_tianhai = 0", 1) }
	if isDraw == 2 { db.Where("is_draw = ?", 0) }
	if isDraw == 3 { db.Where("is_draw = ? and is_tianhai = 8", 1) }
	if isDraw == 4 { db.Where("is_draw = ?", 1) }
	if state > 0 { db.Where("state = ?", state) }
	db.Find(&m)
	return dingo.ErrorIf(db.Error, m)
}

// QueryTotalOrder 查询订单总数-订单来源
func (d *StatisticsDao) QueryTotalOrderByOrderFrom(f, dateStr string) *dingo.ErrorWrap {
	var m CountSettlement
	db := global.Orm.Table("temp_orders_" + helper.ConvSpecStr(dateStr)).Select("count(1) as total, SUM(settlement_money) as money")
	if f != "" { db.Where("order_from = ?", f) }
	db.Find(&m)
	return dingo.ErrorIf(db.Error, m)
}

// QueryTotalOrder 查询订单总数-订单支付方式
func (d *StatisticsDao) QueryTotalOrderByPayFrom(p int, dateStr string) *dingo.ErrorWrap {
	var m CountSettlement
	db := global.Orm.Table("temp_orders_" + helper.ConvSpecStr(dateStr)).Select("count(1) as total, SUM(settlement_money) as money")
	if p > 0 { db.Where("pay_from = ?", p) }
	db.Find(&m)
	return dingo.ErrorIf(db.Error, m)
}

// QueryTotalOrderByCreate 查询创建时间订单总数
func (d *StatisticsDao) QueryTotalOrderByCreate(dateStr string) *dingo.ErrorWrap {
	var total int64
	db := global.Orm.Table("orders").Select("count(1) as total")
	//db.Where("create_at >= ? and create_at <= ?", beginTime, endTime)
	db.Where("DATE_FORMAT(FROM_UNIXTIME(create_at),'%Y-%m-%d') = ?", dateStr)
	db.Find(&total)
	return dingo.ErrorIf(db.Error, total)
}

// QueryTotalOrderGroupByPlaceId 查询门店的订单总数
func (d *StatisticsDao) QueryTotalOrderGroupByPlaceId(dateStr string) *dingo.ErrorWrap {
	var total int64
	sql := "SELECT count(1) as total FROM (SELECT place_id,count(1) FROM temp_orders_" + helper.ConvSpecStr(dateStr) + " GROUP BY place_id) as a"
	db := global.Orm.Raw(sql).Find(&total)
	return dingo.ErrorIf(db.Error, total)
}

// QueryTotalOrderGroupByDevice 查询设备的订单总数
func (d *StatisticsDao) QueryTotalOrderGroupByDevice(dateStr string) *dingo.ErrorWrap {
	var total int64
	sql := "SELECT count(1) as total FROM (SELECT place_id,count(1) FROM temp_orders_" + helper.ConvSpecStr(dateStr) + " GROUP BY device_sn) as a"
	db := global.Orm.Raw(sql).Find(&total)
	return dingo.ErrorIf(db.Error, total)
}

// QueryTotalSettlement 查询订单总流水
func (d *StatisticsDao) QueryTotalSettlement(beginTime, endTime int64, state, isDraw, p int, f string) *dingo.ErrorWrap {
	var total int64

	db := global.Orm.Table("orders").Select("IFNULL(SUM(settlement_money), 0) as total")
	db.Where("end_at >= ? and end_at <= ?", beginTime, endTime)
	if isDraw == 1 { db.Where("is_draw = ? and is_tianhai = 0", 1) }
	if isDraw == 2 { db.Where("is_draw = ?", 0) }
	if isDraw == 3 { db.Where("is_draw = ? and is_tianhai = 8", 1) }
	if state > 0 { db.Where("state = ?", state) } // 按状态查询
	if p > 0 { db.Where("pay_from = ?", p) }
	if f != "" { db.Where("order_from = ?", f) }
	db.Find(&total)
	return dingo.ErrorIf(db.Error, total)
}

// QueryTotalCashLog 查询提现数据
func (d *StatisticsDao) QueryTotalCashLog(dateStr string, t, state int) *dingo.ErrorWrap {
	var m CountSettlement

	db := global.Orm.Table("cash_log").Select("count(1) as total, SUM(transfer_money) as money")
	db.Where("transfer_type = ?", t)
	db.Where("DATE_FORMAT(FROM_UNIXTIME(create_at),'%Y-%m-%d') = ?", dateStr)
	if state > 0 {
		db.Where("state = ?", state)
	}
	db.Find(&m)
	return dingo.ErrorIf(db.Error, m)
}

// QueryTotalIncomeByIsAdmin 查询总收益
func (d *StatisticsDao) QueryTotalIncomeByIsAdmin(isAdmin int, dateStr string) *dingo.ErrorWrap {
	var total int64
	db := global.Orm.Table("temp_dividends_log_" + helper.ConvSpecStr(dateStr)).Select("IFNULL(SUM(money), 0) as total")
	if isAdmin > 0 { db.Where("user_id in (select id from users where is_admin = ?)", isAdmin) }
	db.Find(&total)
	return dingo.ErrorIf(db.Error, total)
}

// QueryTotalIncomeByUserId 查询总收益
func (d *StatisticsDao) QueryTotalIncomeByUserId(beginTime, endTime int64, userId int) *dingo.ErrorWrap {
	var total int64

	db := global.Orm.Table("dividends_log").Select("IFNULL(SUM(money), 0) as total")
	db.Where("create_at >= ? and create_at <= ?", beginTime, endTime)
	db.Where("user_id = ?", userId)

	db.Find(&total)
	return dingo.ErrorIf(db.Error, total)
}

// InsertStatistics 统计数据新增入库
func (d *StatisticsDao) InsertStatistics(m *statistics.StatisticsModel) *dingo.ErrorWrap {
	info := &statistics.StatisticsModel{}
	global.Orm.Table("statistics").
		Where("date_str = ?", m.DateStr).Find(&info)
	db := global.Orm.Table("statistics")
	if info.ID > 0 {
		m.Devices = info.Devices
		m.Places = info.Places
		db.Where("date_str = ?", m.DateStr).Updates(m)
	} else {
		db.Create(m)
	}
	return dingo.ErrorIf(db.Error, true)
}

// InsertStatisticsUser 统计数据新增入库
func (d *StatisticsDao) InsertStatisticsUser(m *statistics.StatisticsUserModel) *dingo.ErrorWrap {
	info := &statistics.StatisticsUserModel{}
	var c int64
	global.Orm.Table("statistics_user").
		Where("date_str = ?", m.DateStr).
		Where("user_id = ?", m.UserId).Find(&info)
	log.Println(m.DateStr, m.UserId, info)
	db := global.Orm.Table("statistics_user")
	if info.ID > 0 {
		m.BranchPlaces = info.BranchPlaces
		m.TotalPlace = info.TotalPlace
		m.TotalDirectPlace = info.TotalDirectPlace
		m.TotalDevices = info.TotalDevices
		m.TotalTeam = info.TotalTeam
		m.TotalDirectTeam = info.TotalDirectTeam
		db.Where("date_str = ?", m.DateStr).Where("user_id = ?", m.UserId).Updates(m)
	} else {
		global.Orm.Table("places").Where("branch_user_id = ?", m.UserId).Count(&c)
		m.BranchPlaces = int(c)
		db.Create(m)
	}
	//db := global.Orm.Table("statistics_user").FirstOrCreate(m)
	return dingo.ErrorIf(db.Error, true)
}

func (d *StatisticsDao) InsertStatisticsPlaceUser(m *statistics.StatisticsPlaceUserModel) *dingo.ErrorWrap {
	info := &statistics.StatisticsPlaceUserModel{}
	var c int64
	global.Orm.Table("statistics_place_user").
		Where("date_str = ?", m.DateStr).
		Where("user_id = ?", m.UserId).Find(&info)
	log.Println(m.DateStr, m.UserId, info)
	db := global.Orm.Table("statistics_place_user")
	if info.ID > 0 {
		m.BranchPlaces = info.BranchPlaces
		m.TotalPlace = info.TotalPlace
		m.TotalDirectPlace = info.TotalDirectPlace
		m.TotalDevices = info.TotalDevices
		m.TotalTeam = info.TotalTeam
		m.TotalDirectTeam = info.TotalDirectTeam
		db.Where("date_str = ?", m.DateStr).Where("user_id = ?", m.UserId).Updates(m)
	} else {
		global.Orm.Table("places").Where("branch_user_id = ?", m.UserId).Count(&c)
		m.BranchPlaces = int(c)
		db.Create(m)
	}
	//db := global.Orm.Table("statistics_user").FirstOrCreate(m)
	return dingo.ErrorIf(db.Error, true)
}

// InsertOrderToTemp 订单数据新增至临时表
func (d *StatisticsDao) InsertOrderToTemp(dateStr string) *dingo.ErrorWrap {
	global.Orm.Exec("DROP TABLE IF EXISTS temp_orders_" + helper.ConvSpecStr(dateStr) + ";")
	global.Orm.Exec("CREATE TABLE temp_orders_" + helper.ConvSpecStr(dateStr) + " LIKE orders;")
	//db := global.Orm.Exec("INSERT INTO temp_orders SELECT * FROM `orders` WHERE end_at >= ? and end_at <= ?", beginTime, endTime)
	db := global.Orm.Exec("INSERT INTO temp_orders_" + helper.ConvSpecStr(dateStr) + " SELECT * FROM `orders` WHERE DATE_FORMAT(FROM_UNIXTIME(end_at),'%Y-%m-%d') = ?", dateStr)
	return dingo.ErrorIf(db.Error, true)
}

// InsertDividendsToTemp 分红数据新增至临时表
func (d *StatisticsDao) InsertDividendsToTemp(dateStr string) *dingo.ErrorWrap {
	global.Orm.Exec("DROP TABLE IF EXISTS temp_dividends_log_" + helper.ConvSpecStr(dateStr) + ";")
	global.Orm.Exec("CREATE TABLE temp_dividends_log_" + helper.ConvSpecStr(dateStr) + " LIKE dividends_log;")
	//db := global.Orm.Exec("INSERT INTO temp_dividends_log SELECT * FROM `dividends_log` WHERE create_at >= ? and create_at <= ?", beginTime, endTime)
	db := global.Orm.Exec("INSERT INTO temp_dividends_log_" + helper.ConvSpecStr(dateStr) + " SELECT * FROM `dividends_log` WHERE DATE_FORMAT(FROM_UNIXTIME(create_at),'%Y-%m-%d') = ?", dateStr)
	return dingo.ErrorIf(db.Error, true)
}

// InsertOrdersUserRelevancyToTemp 关系表数据新增至临时表
func (d *StatisticsDao) InsertOrdersUserRelevancyToTemp(dateStr string) *dingo.ErrorWrap {
	global.Orm.Exec("DROP TABLE IF EXISTS temp_orders_user_relevancy_" + helper.ConvSpecStr(dateStr) + ";")
	global.Orm.Exec("CREATE TABLE temp_orders_user_relevancy_" + helper.ConvSpecStr(dateStr) + " LIKE orders_user_relevancy;")
	db := global.Orm.Exec("INSERT INTO temp_orders_user_relevancy_" + helper.ConvSpecStr(dateStr) + " SELECT * FROM `orders_user_relevancy` WHERE end_date = ?", dateStr)
	return dingo.ErrorIf(db.Error, true)
}

// 清除临时表
func (d *StatisticsDao) DeleteTempTable(dateStr string) *dingo.ErrorWrap {
	global.Orm.Exec("DROP TABLE IF EXISTS temp_orders_" + helper.ConvSpecStr(dateStr) + ";")
	global.Orm.Exec("DROP TABLE IF EXISTS temp_orders_user_relevancy_" + helper.ConvSpecStr(dateStr) + ";")
	db := global.Orm.Exec("DROP TABLE IF EXISTS temp_dividends_log_" + helper.ConvSpecStr(dateStr) + ";")
	return dingo.ErrorIf(db.Error, true)
}

// QueryUserTotalOrderGroupByPlaceId 查询门店的订单总数
func (d *StatisticsDao) QueryUserTotalOrderGroupByPlaceId(dateStr string) *dingo.ErrorWrap {
	var total int64
	sql := "SELECT count(1) as total FROM (SELECT place_id,count(1) FROM temp_orders_" + helper.ConvSpecStr(dateStr) + " GROUP BY place_id) as a"
	db := global.Orm.Raw(sql).Find(&total)
	return dingo.ErrorIf(db.Error, total)
}

// 获取每天关联用户
func (d *StatisticsDao) GetUserIdsByOrders(dateStr string) *dingo.ErrorWrap {
	var ids []int
	//sql := "SELECT user_id FROM temp_orders_user_relevancy GROUP BY user_id"
	sql := "SELECT tour.user_id FROM temp_orders_user_relevancy_" + helper.ConvSpecStr(dateStr) + " tour LEFT JOIN users u ON tour.user_id = u.id " +
		"WHERE u.is_admin NOT IN (1,4) GROUP BY tour.user_id"
	db := global.Orm.Raw(sql).Find(&ids)
	return dingo.ErrorIf(db.Error, ids)
}

// 直属下级数
func (d *StatisticsDao) CountUsersByParentId(ids []int, dateStr string) *dingo.ErrorWrap {
	var total []int
	db := global.Orm.Table("users").
		Select("id").
		Where("parent_id in (?)", ids).
		Where("DATE_FORMAT(FROM_UNIXTIME(create_at),'%Y-%m-%d') <= ?", dateStr).Find(&total)
	//sql := "SELECT id FROM users WHERE parent_id = ?"
	//db := global.Orm.Raw(sql, id).Find(&total)
	return dingo.ErrorIf(db.Error, total)
}

// 直属门店数
func (d *StatisticsDao) CountPlacesByUserId(id int, dateStr string) *dingo.ErrorWrap {
	var total []int
	db := global.Orm.Table("places").
		Select("id").
		Where("user_id = ?", id).
		Where("DATE_FORMAT(FROM_UNIXTIME(create_at),'%Y-%m-%d') <= ?", dateStr).Find(&total)
	//sql := "SELECT id FROM places WHERE user_id = ?"
	//db := global.Orm.Raw(sql, id).Find(&total)
	return dingo.ErrorIf(db.Error, total)
}

// 获取下级门店数
func (d *StatisticsDao) CountPlacesByUserIds(ids []int, dateStr string) *dingo.ErrorWrap {
	var total []int
	db := global.Orm.Table("places").
		Select("id").
		Where("user_id in (?)", ids).
		Where("DATE_FORMAT(FROM_UNIXTIME(create_at),'%Y-%m-%d') <= ?", dateStr).
		Find(&total)
	return dingo.ErrorIf(db.Error, total)
}

// 获取下级门店设备总数
func (d *StatisticsDao) CountDevicesByPlaceIds(placeIds []int) *dingo.ErrorWrap {
	var total int64
	db := global.Orm.Table("devices").Where("place_id in (?)", placeIds).Count(&total)
	return dingo.ErrorIf(db.Error, total)
}

// QueryTotalOrderByState 查询订单总数-状态
func (d *StatisticsDao) QueryUserTotalOrderByState(state, id int, dateStr string) *dingo.ErrorWrap {
	var m CountSettlement
	//sql := "SELECT tour.trade_no FROM temp_orders_user_relevancy tour LEFT JOIN temp_orders o ON tour.trade_no = o.trade_no WHERE tour.user_id = ? and o.state = ?"
	db := global.Orm.Table("temp_orders_user_relevancy_" + helper.ConvSpecStr(dateStr) + " tour").
		Select("count(1) as total, SUM(o.settlement_money) as money").
		Joins("LEFT JOIN temp_orders_" + helper.ConvSpecStr(dateStr) + " o ON tour.trade_no = o.trade_no")
	db.Where("tour.user_id = ?", id)
	if state > 0 { db.Where("o.state = ?", state) } // 按状态查询
	db.Where("o.is_tianhai = 0")
	db.Find(&m)
	return dingo.ErrorIf(db.Error, m)
}

// QueryUserTotalOrderByDraw 查询订单总数-抽单
func (d *StatisticsDao) QueryUserTotalOrderByDraw(isDraw, state, id int, dateStr string) *dingo.ErrorWrap {
	var m CountSettlement
	db := global.Orm.Table("temp_orders_user_relevancy_" + helper.ConvSpecStr(dateStr) + " tour").
		Select("count(1) as total, SUM(o.settlement_money) as money").
		Joins("LEFT JOIN temp_orders_" + helper.ConvSpecStr(dateStr) + " o ON tour.trade_no = o.trade_no")
	if isDraw == 1 { db.Where("o.is_draw = ? and o.is_tianhai = 0", 1) }
	if isDraw == 2 { db.Where("o.is_draw = ?", 0) }
	if isDraw == 3 { db.Where("o.is_draw = ? and o.is_tianhai = 8", 1) }
	if isDraw == 4 { db.Where("o.is_draw = ?", 1) }
	if state > 0 { db.Where("o.state = ?", state) }
	db.Where("tour.user_id = ?", id)
	db.Find(&m)
	return dingo.ErrorIf(db.Error, m)
}

func (d *StatisticsDao) GetOtherUserIdsByIds(ids []int, dateStr string) *dingo.ErrorWrap {
	var userIds []int
	sql := "SELECT id FROM users WHERE id NOT IN ? and is_admin NOT IN (1,2,4) and (FIND_IN_SET('zmadmin', platform) or FIND_IN_SET('hotel', platform)) and DATE_FORMAT(FROM_UNIXTIME(create_at),'%Y-%m-%d') <= ?"
	db := global.Orm.Raw(sql, ids, dateStr).Find(&userIds)
	return dingo.ErrorIf(db.Error, userIds)

	//db := global.Orm.Table("users").Select("id")
	//db.Where("id NOT IN (?)", ids)
	//db.Where("is_admin != 1")
	//db.Where("is_admin != 2")
	//db.Where("is_admin != 4")
	//db.Where("DATE_FORMAT(FROM_UNIXTIME(create_at),'%Y-%m-%d') <= ?", dateStr)
	//db.Find(&userIds)
	//return dingo.ErrorIf(db.Error, userIds)
}

func (d *StatisticsDao) QueryUserTotalIncomeByUserId(userId int, dateStr string) *dingo.ErrorWrap {
	var total int64
	db := global.Orm.Table("temp_dividends_log_" + helper.ConvSpecStr(dateStr)).Select("IFNULL(SUM(money), 0) as total")
	db.Where("user_id = ?", userId)

	db.Find(&total)
	return dingo.ErrorIf(db.Error, total)
}

// QueryUserTotalCashLog 查询提现数据
func (d *StatisticsDao) QueryUserTotalCashLog(dateStr string, t, state, id int) *dingo.ErrorWrap {
	var m CountSettlement

	db := global.Orm.Table("cash_log").Select("count(1) as total, SUM(transfer_money) as money")
	db.Where("transfer_type = ?", t)
	db.Where("DATE_FORMAT(FROM_UNIXTIME(create_at),'%Y-%m-%d') = ?", dateStr)
	if state > 0 {
		db.Where("state = ?", state)
	}
	db.Where("user_id = ?", id)
	db.Find(&m)
	return dingo.ErrorIf(db.Error, m)
}

// QueryPlaceSalesByUserId 获取用户下的动销门店
func (d *StatisticsDao) QueryPlaceSalesByUserId(dateStr string, userId int) *dingo.ErrorWrap {
	var total int64
	sql := "SELECT count(1) as total FROM (SELECT o.place_id FROM temp_orders_user_relevancy_" + helper.ConvSpecStr(dateStr) + " tour " +
		"LEFT JOIN temp_orders_" + helper.ConvSpecStr(dateStr) + " o ON tour.trade_no = o.trade_no WHERE tour.user_id = ?" +
		" GROUP BY o.place_id) as a"
	db := global.Orm.Raw(sql, userId).Find(&total)
	return dingo.ErrorIf(db.Error, total)
}



// ---------------------------------------- 获取统计表 数据 ---------------------------------- //

// 获取基础数据
func (d *StatisticsDao) GetStatisticsDataByDay(dateStr string) *dingo.ErrorWrap {
	m := &statistics.BasicAnalysis{}
	db := global.Orm.Table("statistics")
	db.Where("date_str = ?", dateStr)
	db.Limit(1)
	db.Find(&m)
	return dingo.ErrorIf(db.Error, m)
}

// 获取基础数据
func (d *StatisticsDao) GetStatisticsNormalDataByDay(dateStr string) *dingo.ErrorWrap {
	m := &statistics.BasicAnalysisNormal{}
	db := global.Orm.Table("statistics")
	db.Where("date_str = ?", dateStr)
	db.Limit(1)
	db.Find(&m)
	return dingo.ErrorIf(db.Error, m)
}

// 获取流水折线图
func (d *StatisticsDao) GetSettlementChart() *dingo.ErrorWrap {
	var list []statistics.SettlementChart
	db := global.Orm.Table("statistics").
		Select("settlement_total/100 as settlement_total," +
			" settlement_wx/100 as wx_settlement_total," +
			" settlement_zfb/100 as zfb_settlement_total," +
			" settlement_alldraw/100 as draw_settlement_total, date_str")
	// 获取最近 30 天日期
	startDate := time.Now().AddDate(0, 0, -30).Format("2006-01-02")
	endDate := time.Now().AddDate(0, 0, -1).Format("2006-01-02") // 昨日
	db.Where("date_str >= ?", startDate)
	db.Where("date_str <= ?", endDate)

	db.Find(&list)
	return dingo.ErrorIf(db.Error, list)
}

// 获取流水折线图
func (d *StatisticsDao) GetSettlementNormalChart() *dingo.ErrorWrap {
	var list []statistics.SettlementNormalChart
	db := global.Orm.Table("statistics").
		Select("settlement_complete/100 as settlement_complete, income_business/100 as income_business, date_str")
	// 获取最近 30 天日期
	startDate := time.Now().AddDate(0, 0, -30).Format("2006-01-02")
	endDate := time.Now().AddDate(0, 0, -1).Format("2006-01-02") // 昨日
	db.Where("date_str >= ?", startDate)
	db.Where("date_str <= ?", endDate)

	db.Find(&list)
	return dingo.ErrorIf(db.Error, list)
}

// 获取收益折线图
func (d *StatisticsDao) GetIncomeChart() *dingo.ErrorWrap {
	var list []statistics.IncomeChart
	db := global.Orm.Table("statistics").
		Select("income_total/100 as income_total," +
			" income_business/100 as income_business," +
			" income_branch/100 as income_branch," +
			" (wx_ad/100 + zfb_ad/100) as income_ad, date_str")
	// 获取最近 30 天日期
	startDate := time.Now().AddDate(0, 0, -30).Format("2006-01-02")
	endDate := time.Now().AddDate(0, 0, -1).Format("2006-01-02") // 昨日
	db.Where("date_str >= ?", startDate)
	db.Where("date_str <= ?", endDate)

	db.Find(&list)
	return dingo.ErrorIf(db.Error, list)
}

// 广告收益图表
func (d *StatisticsDao) GetAdIncomeChart() *dingo.ErrorWrap {
	var list []statistics.AdIncomeChart
	db := global.Orm.Table("statistics").
		Select("date_str, FORMAT(IFNULL((wx_ad+zfb_ad)/100/(wx_uv+zfb_uv),0),2) as uv_price," +
			"FORMAT(IFNULL((wx_ad+zfb_ad)/100/count_end,0),2) as total_price," +
			"(wx_ad/100 + zfb_ad/100) as money")
	startDate := time.Now().AddDate(0, 0, -30).Format("2006-01-02")
	endDate := time.Now().AddDate(0, 0, -1).Format("2006-01-02")  // 昨日
	db.Where("date_str >= ?", startDate)
	db.Where("date_str <= ?", endDate)

	db.Find(&list)
	return dingo.ErrorIf(db.Error, list)
}

// 提现
func (d *StatisticsDao) GetCashChart() *dingo.ErrorWrap {
	var list []statistics.CashChart
	db := global.Orm.Table("statistics").
		Select("date_str," +
			"(weixin_total+qrcode_success_total) as total," +
			"(weixin_money+qrcode_success_money)/100 as money")
	startDate := time.Now().AddDate(0, 0, -15).Format("2006-01-02")
	endDate := time.Now().AddDate(0, 0, -1).Format("2006-01-02")  // 昨日
	db.Where("date_str >= ?", startDate)
	db.Where("date_str <= ?", endDate)

	db.Find(&list)
	return dingo.ErrorIf(db.Error, list)
}

// 获取用户
func (d *StatisticsDao) GetUvChart() *dingo.ErrorWrap {
	var list []statistics.UvChart
	db := global.Orm.Table("statistics").
		Select("date_str," +
			"(wx_uv+zfb_uv) as count, wx_uv, zfb_uv, uv_count, pv_count")
	startDate := time.Now().AddDate(0, 0, -15).Format("2006-01-02")
	endDate := time.Now().AddDate(0, 0, -1).Format("2006-01-02")  // 昨日
	db.Where("date_str >= ?", startDate)
	db.Where("date_str <= ?", endDate)

	db.Find(&list)
	return dingo.ErrorIf(db.Error, list)
}

// 单价折线图
func (d *StatisticsDao) GetPriceChart() *dingo.ErrorWrap {
	var list []statistics.PriceChart
	db := global.Orm.Table("statistics").
		Select("date_str, count_complete as order_total, FORMAT(IFNULL((settlement_total/100)/count_complete,0),2) as price")
	// 获取最近 10 天日期
	startDate := time.Now().AddDate(0, 0, -10).Format("2006-01-02")
	endDate := time.Now().AddDate(0, 0, -1).Format("2006-01-02")   // 昨日
	db.Where("date_str >= ?", startDate)
	db.Where("date_str <= ?", endDate)

	db.Find(&list)
	return dingo.ErrorIf(db.Error, list)
}

// 单价折线图
func (d *StatisticsDao) GetPriceChartNormal() *dingo.ErrorWrap {
	var list []statistics.PriceChart
	db := global.Orm.Table("statistics").
		Select("date_str, count_complete as order_total, FORMAT(IFNULL((settlement_complete/100)/count_complete,0),2) as price")
	// 获取最近 10 天日期
	startDate := time.Now().AddDate(0, 0, -10).Format("2006-01-02")
	endDate := time.Now().AddDate(0, 0, -1).Format("2006-01-02")   // 昨日
	db.Where("date_str >= ?", startDate)
	db.Where("date_str <= ?", endDate)

	db.Find(&list)
	return dingo.ErrorIf(db.Error, list)
}

// 门店动销、设备动销
func (d *StatisticsDao) GetSaleChartByDay() *dingo.ErrorWrap {
	var list []statistics.SaleChart
	db := global.Orm.Table("statistics").
		Select("date_str, count_place, count_device")
	// 获取最近 10 天日期
	startDate := time.Now().AddDate(0, 0, -15).Format("2006-01-02")
	endDate := time.Now().AddDate(0, 0, -1).Format("2006-01-02")   // 昨日
	db.Where("date_str >= ?", startDate)
	db.Where("date_str <= ?", endDate)

	db.Find(&list)
	return dingo.ErrorIf(db.Error, list)
}

// 门店动销率、设备动销率
func (d *StatisticsDao) GetSaleRateChartByDay() *dingo.ErrorWrap {
	var list []statistics.SaleRateChart
	db := global.Orm.Table("statistics").
		Select("date_str, FORMAT(IFNULL(count_place/places,0),2) as place_rate, FORMAT(IFNULL(count_device/devices,0),2) as device_rate")
	// 获取最近 10 天日期
	startDate := time.Now().AddDate(0, 0, -15).Format("2006-01-02")
	endDate := time.Now().AddDate(0, 0, -1).Format("2006-01-02")   // 昨日
	db.Where("date_str >= ?", startDate)
	db.Where("date_str <= ?", endDate)

	db.Find(&list)
	return dingo.ErrorIf(db.Error, list)
}

// 扫码量
func (d *StatisticsDao) GetScanChartByDate() *dingo.ErrorWrap {
	var list []statistics.ScanChart
	db := global.Orm.Table("statistics").
		Select("date_str, pv_count, uv_count")
	// 获取最近 10 天日期
	startDate := time.Now().AddDate(0, 0, -15).Format("2006-01-02")
	endDate := time.Now().AddDate(0, 0, -1).Format("2006-01-02")   // 昨日
	db.Where("date_str >= ?", startDate)
	db.Where("date_str <= ?", endDate)

	db.Find(&list)
	return dingo.ErrorIf(db.Error, list)
}

// 转化率
func (d *StatisticsDao) GetConvRateChartByDate() *dingo.ErrorWrap {
	var list []statistics.ConvRateChart
	db := global.Orm.Table("statistics").
		Select("date_str, FORMAT(IFNULL(count_create/uv_count*100,0),2) as rate")
	startDate := time.Now().AddDate(0, 0, -15).Format("2006-01-02")
	endDate := time.Now().AddDate(0, 0, -1).Format("2006-01-02")   // 昨日
	db.Where("date_str >= ?", startDate)
	db.Where("date_str <= ?", endDate)

	db.Find(&list)
	return dingo.ErrorIf(db.Error, list)
}

// 基础数据分析列表
func (d *StatisticsDao) GetAnalysisList(param *request.AnalysisListReq) *dingo.ErrorWrap {

	var (
		lst    []*statistics.StatisticsModel
		count  int64
		limit  int = param.PageSize
		offset int = param.PageSize * (param.PageNo - 1)
	)

	db := global.Orm.Debug().Table("statistics")
	if param.BeginTime != "" {
		dateRange := strings.Split(param.BeginTime, ",")
		db.Where("date_str >= ?", dateRange[0])
		db.Where("date_str <= ?", dateRange[1])
	}
	db.Order("date_str DESC")

	//db.Limit(limit).Offset(offset).Find(&lst).Limit(-1).Offset(-1).Count(&count)
	db.Limit(limit).Offset(offset).Find(&lst)
	if param.CountStatus != helper.TypeNumCountStatusNo {
		db.Limit(-1).Offset(-1).Count(&count)
	}
	return dingo.ErrorIf(db.Error, lst, count)
}

// UV 数据录入
func (d *StatisticsDao) UpdateUvData(param *request.UvDataInsertReq) *dingo.ErrorWrap {
	m := &statistics.StatisticsModel{}
	m.WxUv = param.WxUv
	m.WxNewUv = param.WxNewUv
	m.ZfbUv = param.ZfbUv
	m.ZfbNewUv = param.ZfbNewUv
	m.WxAd = param.WxAd
	m.ZfbAd = param.ZfbAd
	m.WxKs = param.WxKs

	db := global.Orm.Table("statistics").
		Select("wx_uv", "wx_new_uv", "zfb_uv", "zfb_new_uv", "wx_ad", "zfb_ad", "wx_ks").
		Where("date_str = ?", param.DateStr).
		Updates(&m)
	return dingo.ErrorIf(db.Error, true)
}

// 用户数据分析列表
func (d *StatisticsDao) GetAnalysisUserList(param *request.AnalysisUserListReq) *dingo.ErrorWrap {

	var (
		lst    []*statistics.StatisticsUserList
		count  int64
		limit  int = param.PageSize
		offset int = param.PageSize * (param.PageNo - 1)
	)

	db := global.Orm.Debug().Table("statistics_user su").
		Select("su.*", "u.name", "u.phone", "u.user_no", "u.user_name", "u.is_admin").
		Joins("LEFT JOIN users u ON su.user_id = u.id")
	if param.IsAdmin > 0 {
		db.Where("u.is_admin = ?", param.IsAdmin)
	}
	if param.UserId > 0 {
		db.Where("su.user_id = ?", param.UserId)
	}
	if param.BeginTime != "" {
		dateRange := strings.Split(param.BeginTime, ",")
		db.Where("su.date_str >= ?", dateRange[0])
		db.Where("su.date_str <= ?", dateRange[1])
	}
	db.Order("su.date_str DESC")

	db.Limit(limit).Offset(offset).Find(&lst)
	if param.CountStatus != helper.TypeNumCountStatusNo {
		db.Limit(-1).Offset(-1).Count(&count)
	}
	return dingo.ErrorIf(db.Error, lst, count)
}

func (d *StatisticsDao) GetUserTeamRankList(param *request.AnalysisUserListReq, dateStr string) *dingo.ErrorWrap {
	var (
		lst    []*statistics.StatisticsUserTeamRankList
		count  int64
		limit  int = param.PageSize
		offset int = param.PageSize * (param.PageNo - 1)
	)
	db := global.Orm.Table("statistics_user su").
		Select("su.total_team, su.total_direct_team," +
			"su.user_id, u.name, u.user_name, u.phone").
		Joins("LEFT JOIN users u ON su.user_id = u.id").
		Where("u.is_admin = 8").Where("su.date_str = ?", dateStr)

	db.Order("su.total_team DESC")

	db.Limit(limit).Offset(offset).Find(&lst)
	if param.CountStatus != helper.TypeNumCountStatusNo {
		db.Limit(-1).Offset(-1).Count(&count)
	}
	return dingo.ErrorIf(db.Error, lst, count)
}

func (d *StatisticsDao) GetUserPlaceRankList(param *request.AnalysisUserListReq, dateStr string) *dingo.ErrorWrap {
	var (
		lst    []*statistics.StatisticsUserPlaceRankList
		count  int64
		limit  int = param.PageSize
		offset int = param.PageSize * (param.PageNo - 1)
	)
	db := global.Orm.Table("statistics_user su").
		Select("su.total_place, su.total_direct_place," +
			"su.user_id, u.name, u.user_name, u.phone").
		Joins("LEFT JOIN users u ON su.user_id = u.id").
		Where("u.is_admin = 8").Where("su.date_str = ?", dateStr)

	db.Order("su.total_place DESC")

	db.Limit(limit).Offset(offset).Find(&lst)
	if param.CountStatus != helper.TypeNumCountStatusNo {
		db.Limit(-1).Offset(-1).Count(&count)
	}
	return dingo.ErrorIf(db.Error, lst, count)
}

func (d *StatisticsDao) GetUserSettlementRankList(param *request.AnalysisUserListReq) *dingo.ErrorWrap {
	var (
		lst    []*statistics.StatisticsUserSettlementRankList
		count  int64
		limit  int = param.PageSize
		offset int = param.PageSize * (param.PageNo - 1)
	)
	db := global.Orm.Table("statistics_user su").
		Select("SUM(su.settlement_total) as settlement_total,SUM(su.settlement_draw) as settlement_draw," +
			"SUM(su.settlement_smalldraw) as settlement_smalldraw,SUM(su.settlement_nodraw) as settlement_nodraw," +
			"su.user_id, u.name, u.user_name, u.phone").
		Joins("LEFT JOIN users u ON su.user_id = u.id").
		Where("u.is_admin = 8")
	db.Group("su.user_id")
	db.Order("settlement_total DESC")

	db.Limit(limit).Offset(offset).Find(&lst)
	if param.CountStatus != helper.TypeNumCountStatusNo {
		db.Limit(-1).Offset(-1).Count(&count)
	}
	return dingo.ErrorIf(db.Error, lst, count)
}

func (d *StatisticsDao) GetUserIncomeRankList(param *request.AnalysisUserListReq) *dingo.ErrorWrap {
	var (
		lst    []*statistics.StatisticsUserIncomeRankList
		count  int64
		limit  int = param.PageSize
		offset int = param.PageSize * (param.PageNo - 1)
	)
	db := global.Orm.Table("statistics_user su").
		Select("SUM(su.total_income) as total_income," +
			"su.user_id, u.name, u.user_name, u.phone").
		Joins("LEFT JOIN users u ON su.user_id = u.id").
		Where("u.is_admin = 8")
	db.Group("su.user_id")
	db.Order("total_income DESC")

	db.Limit(limit).Offset(offset).Find(&lst)
	if param.CountStatus != helper.TypeNumCountStatusNo {
		db.Limit(-1).Offset(-1).Count(&count)
	}
	return dingo.ErrorIf(db.Error, lst, count)
}

func (d *StatisticsDao) GetScanPvByDateStr(dateStr string) *dingo.ErrorWrap {
	m := &statistics.ScanChart{}
	sql := "SELECT count(1) as pv_count, count(DISTINCT openid) as uv_count FROM scan_logs " +
		"WHERE DATE_FORMAT(FROM_UNIXTIME(create_at), '%Y-%m-%d') = ? "
	db := global.Orm.Raw(sql, dateStr)
	db.Find(&m)
	return dingo.ErrorIf(db.Error, m)
}

// 扫码曲线
func (d *StatisticsDao) GetScanChartByDay() *dingo.ErrorWrap {
	var list []*statistics.ScanChart
	//startDate := time.Now().AddDate(0, 0, -30).Format("2006-01-02")
	//endDate := time.Now().AddDate(0, 0, -1).Format("2006-01-02")   // 昨日
	//sql := "SELECT DATE_FORMAT(FROM_UNIXTIME(create_at), '%Y-%m-%d') as date_str, count(1) as pv_count, count(DISTINCT openid) as uv_count FROM scan_logs " +
	//	"WHERE (DATE_FORMAT(FROM_UNIXTIME(create_at), '%Y-%m-%d') >= ?) AND (DATE_FORMAT(FROM_UNIXTIME(create_at), '%Y-%m-%d') <= ?) " +
	//	"GROUP BY DATE_FORMAT(FROM_UNIXTIME(create_at), '%Y-%m-%d')"

	startDate, endDate := wechatpay.GetLateDayStartEnd(30)
	sql := "SELECT DATE_FORMAT(FROM_UNIXTIME(create_at), '%Y-%m-%d') as date_str, count(1) as pv_count, count(DISTINCT openid) as uv_count FROM scan_logs " +
		"WHERE (create_at >= ?) AND (create_at <= ?) " +
		"GROUP BY DATE_FORMAT(FROM_UNIXTIME(create_at), '%Y-%m-%d')"
	log.Println(sql)
	db := global.Orm.Raw(sql, startDate, endDate)
	db.Find(&list)
	return dingo.ErrorIf(db.Error, list)
}



// --------------------------------  获取统计表数据 用户 --------------------------------- //

// 获取用户基础数据 某一天
func (d *StatisticsDao) GetUserStatisticsDataByDay(dateStr string, userId int) *dingo.ErrorWrap {
	m := &statistics.UserAnalysisModel{}
	field := `total_place, place_sale,total_devices,  SUM(total_complete) as total_complete, SUM(total_complete_nodraw) as total_complete_nodraw, SUM(total_draw) as total_draw, CONVERT(SUM(total_income)/100, DECIMAL(10, 2)) as total_income, CONVERT(SUM(settlement_complete)/100, DECIMAL(10, 2)) as settlement_complete, CONVERT(SUM(settlement_draw)/100, DECIMAL(10, 2)) as settlement_draw, CONVERT(SUM(settlement_normal_total)/100, DECIMAL(10, 2)) as settlement_normal_total, date_str, user_id`
	db := global.Orm.Table("statistics_user").Select(field)
	db.Where("date_str = ?", dateStr)
	db.Where("user_id = ?", userId)
	db.Limit(1)
	db.Find(&m)
	return dingo.ErrorIf(db.Error, m)
}

// 获取用户基础数据 时间范围内
func (d *StatisticsDao) GetUserStatisticsDataByDayRange(startDate, endDate string, userId int) *dingo.ErrorWrap {
	m := &statistics.UserAnalysisModel{}
	field := `SUM(total_complete) as total_complete, SUM(total_complete_nodraw) as total_complete_nodraw, SUM(total_draw) as total_draw,CONVERT(SUM(total_income)/100, DECIMAL(10, 2)) as total_income, CONVERT(SUM(settlement_complete)/100, DECIMAL(10, 2)) as settlement_complete, CONVERT(SUM(settlement_draw)/100, DECIMAL(10, 2)) as settlement_draw, CONVERT(SUM(settlement_normal_total)/100, DECIMAL(10, 2)) as settlement_normal_total, date_str, user_id`
	db := global.Orm.Table("statistics_user").Select(field)
	db.Where("date_str >= ? and date_str <= ?", startDate, endDate)
	db.Where("user_id = ?", userId)
	db.Limit(1)
	db.Find(&m)
	return dingo.ErrorIf(db.Error, m)
}

// 获取用户基础数据 总
func (d *StatisticsDao) GetUserStatisticsTotalDataByUserId(userId int) *dingo.ErrorWrap {
	m := &statistics.UserAnalysisModel{}
	field := `SUM(total_complete) as total_complete, SUM(total_complete_nodraw) as total_complete_nodraw, SUM(total_draw) as total_draw,CONVERT(SUM(total_income)/100, DECIMAL(10, 2)) as total_income, CONVERT(SUM(settlement_complete)/100, DECIMAL(10, 2)) as settlement_complete, CONVERT(SUM(settlement_draw)/100, DECIMAL(10, 2)) as settlement_draw, CONVERT(SUM(settlement_normal_total)/100, DECIMAL(10, 2)) as settlement_normal_total, date_str, user_id`
	db := global.Orm.Table("statistics_user").Select(field)
	db.Where("user_id = ?", userId)
	db.Limit(1)
	db.Find(&m)
	return dingo.ErrorIf(db.Error, m)
}

// 获取用户基础数据 按天
func (d *StatisticsDao) GetUserStatisticsDayDataByUserId(param *request.AnalysisUserReq, userId int) *dingo.ErrorWrap {
	var (
		lst    []*statistics.UserAnalysisModel
		count  int64
		limit  int = param.PageSize
		offset int = param.PageSize * (param.PageNo - 1)
	)
	field := `total_complete, total_complete_nodraw, total_draw, CONVERT(total_income/100, DECIMAL(10, 2)) as total_income, CONVERT(settlement_complete/100, DECIMAL(10, 2)) as settlement_complete, CONVERT(settlement_draw/100, DECIMAL(10, 2)) as settlement_draw, CONVERT(settlement_normal_total/100, DECIMAL(10, 2)) as settlement_normal_total, date_str, user_id`
	db := global.Orm.Debug().Table("statistics_user").Select(field)
	db.Where("user_id = ?", userId)
	db.Order("date_str DESC")
	db.Limit(limit).Offset(offset).Find(&lst).Limit(-1).Offset(-1).Count(&count)
	return dingo.ErrorIf(db.Error, lst, count)
}

// 获取用户基础数据 按月
func (d *StatisticsDao) GetUserStatisticsMonthDataByUserId(param *request.AnalysisUserReq, userId int) *dingo.ErrorWrap {
	var (
		lst    []*statistics.UserAnalysisModel
		count  int64
		limit  int = param.PageSize
		offset int = param.PageSize * (param.PageNo - 1)
	)
	field := `SUM(total_complete) as total_complete, SUM(total_complete_nodraw) as total_complete_nodraw, SUM(total_draw) as total_draw, SUM(settlement_complete)/100 as settlement_complete, SUM(settlement_draw)/100 as settlement_draw, SUM(settlement_normal_total)/100 as settlement_normal, SUM(total_income)/100 as total_income,DATE_FORMAT(date_str,'%Y-%m') as date_str, user_id`
	db := global.Orm.Table("statistics_user").Select(field)
	db.Where("user_id = ?", userId)
	db.Group("DATE_FORMAT(date_str,'%Y-%m')") // 按月排序
	db.Order("date_str DESC")
	db.Limit(limit).Offset(offset).Find(&lst).Limit(-1).Offset(-1).Count(&count)

	return dingo.ErrorIf(db.Error, lst, count)
}

// 获取用户基础数据 按时间范围
func (d *StatisticsDao) GetUserStatisticsRangeDataByUserId(startDate, endDate string, userId int) *dingo.ErrorWrap {
	var lst    []*statistics.UserAnalysisModel
	field := `total_complete, total_complete_nodraw, total_draw, total_place, place_sale, CONVERT(total_income/100, DECIMAL(10, 2)) as total_income, CONVERT(settlement_complete/100, DECIMAL(10, 2)) as settlement_complete, CONVERT(settlement_draw/100, DECIMAL(10, 2)) as settlement_draw, CONVERT(settlement_normal_total/100, DECIMAL(10, 2)) as settlement_normal_total, date_str, user_id`
	db := global.Orm.Table("statistics_user").Select(field)
	db.Where("date_str >= ? and date_str <= ?", startDate, endDate)
	db.Where("user_id = ?", userId)
	db.Find(&lst)
	return dingo.ErrorIf(db.Error, lst)
}

// 获取用户动销数据
//func (d *StatisticsDao) GetSaleChartByDay() *dingo.ErrorWrap {
//	var lst    []*statistics.UserAnalysisModel
//	db := global.Orm.Table("statistics_user").
//		Select("date_str, total_orders, total_devices")
//	// 获取最近 10 天日期
//	startDate := time.Now().AddDate(0, 0, -15).Format("2006-01-02")
//	endDate := time.Now().AddDate(0, 0, -1).Format("2006-01-02")   // 昨日
//	db.Where("date_str >= ?", startDate)
//	db.Where("date_str <= ?", endDate)
//
//	db.Find(&list)
//	return dingo.ErrorIf(db.Error, list)
//}







